const router = require('express').Router();
const { client } = require('../../redis');
const socketio = require('../../socket');
const { dbQuery, dbError, updateToken, sqlStrQuotationHandle } = require('../../tools');


function updateCheckTime(user_id, group_id) {
	if (group_id == '0') {
		// 更新用户查看系统消息的最后时间
		return dbQuery(`update user_table set system_message_check_time=DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') 
			where id='${user_id}'`);
	} else {
		// 更新用户查看群组信息的最后时间
		return dbQuery(`update member_table set check_time=DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') 
			where user_id='${user_id}' and group_id='${group_id}'`);
	}
}

router.use('/join', require('./join'));

router.get('/', function(req ,res) {
	const { user_id } = req.query
	// 查找每组最新一条的消息和群组的信息
	const getLastMessage = dbQuery(`
		select m.message_content,m.send_time,u.name,m.group_id,g.group_name,g.group_avator
		from 
		group_table g,
		message_table m, 
		user_table u,
		(
			select m.group_id, max(m.send_time) as send_time from 
			message_table m, 
			(
				select group_id from member_table where user_id='${user_id}'
			) mb 
			where mb.group_id=m.group_id group by group_id
		) r 
		where r.group_id=m.group_id and r.send_time=m.send_time and m.user_id=u.id and r.group_id=g.group_id
	`);
	// 查找每组有多少未读消息
	const getNewMessage = dbQuery(`
		select count(m.send_time) as no_check,gid.group_id
		from 
		(select check_time, group_id from member_table where user_id='${user_id}') gid,
		(select send_time, group_id from message_table) m
		where gid.group_id=m.group_id and m.send_time > gid.check_time
		GROUP BY gid.group_id
	`);

	Promise.all([getLastMessage, getNewMessage])
	.then(result => {
		const data = result[0].map(val => ({...val, ...result[1].find(v => v.group_id === val.group_id)}));
		res.send({code: 1, message: '获取成功', data})
	})
	.catch(err => {
		dbError(res, err);
	});


	/* 
		// 查找到群组的最后一条消息
		// 查出message_table中send_time最大的项,以group_id分组,就可以找到每组总最大的send_time,既最晚发送的消息
		// 再通过send_time的比较,找到对应数据
		`
		select m.message_id,m.message_content,m.send_time 
		from message_table m, 
		(select group_id, max(send_time) as send_time from message_table group by group_id) c 
		where c.group_id=m.group_id and c.send_time=m.send_time
		`
		// 根据用户id找到群组,在查找到群组的最后一条消息
		// 在找到每组需要的信息前,通过查找member_table以user_id为条件,筛选出需要的group_id作为查找群最后一条消息条件
		`
		select m.message_id,m.message_content,m.send_time,m.group_id from 
		message_table m, 
		(
			select m.group_id, max(m.send_time) as send_time from 
			message_table m, 
			(
				select group_id from member_table where user_id='${id}'
			) mb 
			where mb.group_id=m.group_id group by group_id
		) r 
		where r.group_id=m.group_id and r.send_time=m.send_time
		`
		// 通过member_table中找到的发送user_id,再从user_table中找到name
		// 同理通过message_table找到的group_id和group_table匹配,找到群组信息
		`
		select m.message_content,m.send_time,m.user_id,u.name,m.group_id,g.group_name,g.group_avator
		from 
		group_table g,
		message_table m, 
		user_table u,
		(
			select m.group_id, max(m.send_time) as send_time from 
			message_table m, 
			(
				select group_id from member_table where user_id='1'
			) mb 
			where mb.group_id=m.group_id group by group_id
		) r 
		where r.group_id=m.group_id and r.send_time=m.send_time and m.user_id=u.id and r.group_id=g.group_id
		` 
	 */
});
router.post('/create', async function(req, res) {
	const {id: user_id, name, intro, public} = req.body;
	let introStr = sqlStrQuotationHandle(intro);

	async function canCreateGroup() {
		// 判断用户是否还能创建群组
		let can_create;
		await dbQuery(`select can_create from user_table where id='${user_id}'`)
		.then(data => {
			can_create = data[0].can_create;
		})
		.catch(err => {
			dbError(res, err);
		});
		return can_create;
	}
	// 判断是否能创建新群组
	if (!await canCreateGroup()) {
		return res.send({code: 0, message: '最多创建三个聊天组'});
	}
	function decrementCreateCount() {
		// 减少一次创建次数并更新token
		return dbQuery(`update user_table set can_create=IF(can_create<1, 0, can_create-1) WHERE id='${user_id}'`)
	}
	function updataMember(groupId) {
		// 群组用户更新
		return dbQuery(`insert into member_table (user_id, group_id, check_time) values ('${user_id}', '${groupId}', DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'))`)
	}
	function sendFriMessage(groupId) {
		// 创建成功后向群组中发送一条消息,保证后续查询正常
		return dbQuery(`insert into message_table 
			(group_id, user_id, message_content, send_time)
			values
			('${groupId}', '${user_id}', '我创建了这个新群组', DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'))`)
	}
	// 添加群组信息
	dbQuery(`insert into group_table
		(group_name, group_intro, creator_id, admin, group_public)
		values
		('${name}', '${introStr}', '${user_id}', '${user_id}', '${public}')
	`)
	.then(insertData => {
		// 获取到的刚创建群组的id使用在将群主添加进群主使用
		const groupId = insertData.insertId;
		Promise.all([decrementCreateCount(), updataMember(groupId), sendFriMessage(groupId)])
		.then(() => {
			return updateToken(res, undefined, user_id, {code: 1, message: '创建成功'});
		})
		.catch(err => {
			dbError(res, err);
		});
	})
	.catch(err => {
		dbError(res, err);
	});
});
router.post('/message', async function(req, res){
	const {user_id, group_id, msg} = req.body;
	
	const msgStr = sqlStrQuotationHandle(msg);

	const isJoinGroup = await dbQuery(`select * from member_table where user_id='${user_id}' and group_id='${group_id}'`);
	if (isJoinGroup.length < 1) {
		res.send({code: 0, message: '用户不在群组内'});
		return;
	}

	dbQuery(`insert into message_table 
		(group_id, user_id, message_content, send_time)
		values
		('${group_id}', '${user_id}', '${msgStr}', DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'))`)
	.then(insertData => {
		// 获取发送用户信息后触发socket事件
		dbQuery(`select name,avator from user_table where id='${user_id}'`)
		.then(selectData => {
			// 触发socket事件
			socketio().sockets.to(`room${group_id}`).emit(
				`roomMessage${group_id}`, 
				{
					type: 'message',
					data: {
						user_id: +user_id,
						name: selectData[0].name,
						avator: selectData[0].avator, 
						message_id: insertData.insertId + 'now',
						message_content: msgStr, 
						send_time: new Date()
					}
				}
			);
			res.send({code: 1, messgae: '消息发送成功'});
		})
		.catch(err => {
			dbError(res, err, '消息回调失败');
		});
	})
	.catch(err => {
		dbError(res, err, '消息发送失败');
	});
});
router.get('/check', function(req, res) {
	const {user_id, group_id} = req.query;
	updateCheckTime(user_id, group_id)
	.then(() => res.send({code: 1, message: '更新成功'}))
	.catch(err => dbError(res, err));
});
router.get('/content', function(req, res) {
	const {user_id, group_id} = req.query;
	let limit = req.query.limit || 0;
	if (limit < 0) limit = 0;
	if (group_id === '0') {
		// 系统消息
		dbQuery(`
			select m.*,'system_message' as avator,
			u.name as user_name,
			u.avator as user_avator,
			g.group_name,
			g.group_avator
			from system_message_table m,
			user_table u,
			group_table g
			where 
				m.user_id='${user_id}' and 
				if(
					isnull(m.about_group_id),
					1,
					g.group_id=m.about_group_id
				) and 
				if(
					isnull(m.about_user_id),
					1,
					u.id=m.about_user_id
				)
			GROUP BY m.message_id
			order by m.send_time desc
			limit ${limit},10
		`)
		.then(data => {
			// 更新用户查看系统消息的最后时间
			updateCheckTime(user_id, group_id)
			.catch(err => dbError(res, err));

			if (data.length < 1) {
				return res.send({code: 0, message: '无更多记录'});
			}
			res.send({code: 1, message: '获取成功', data});
		})
		.catch(err => {
			dbError(res, err);
		});
	} else {
		// 群组消息
		// 查找群组内的消息记录,以10分页
		dbQuery(`
			select m.user_id, u.name, u.avator, m.message_id, m.message_content, m.send_time
			from message_table m inner join user_table u on m.user_id=u.id
			where group_id='${group_id}'
			order by m.send_time desc
			limit ${limit},10
		`)
		.then(data => {
			// 更新用户查看群组信息的最后时间
			updateCheckTime(user_id, group_id)
			.catch(err => dbError(res, err));
			if (data.length < 1) {
				return res.send({code: 0, message: '无更多记录'});
			}
			res.send({code: 1, message: '获取群聊记录成功', data});
		})
		.catch(err => {
			dbError(res, err);
		});
	}
});
router.get('/public', function(req, res) {
	const {user_id} = req.query;

	dbQuery(`
		select count(m.user_id) as people_num,m.group_id,g.group_name,g.group_intro,g.group_avator,g.create_time
		from member_table m,
		(
			select group_id,group_name,group_intro,group_avator,create_time
			from 
				group_table
			where group_id not in (select group_id from member_table where user_id='${user_id}') and group_public='1'
		) g
		where g.group_id=m.group_id 
		group by m.group_id
	`)
	.then(data => {
		const result = [...data];
		const sendData = [];
		for (let i = 0; i < (data.length < 12 ? data.length : 12); i ++) {
			sendData.push(result.splice(Math.floor(Math.random() * result.length), 1)[0]);
		}
		res.send({code: 1, message: '获取群组成功', data: sendData});
	})
	.catch(err => {
		dbError(res, err);
	});
});
router.get('/info', function(req, res) {
	const {group_id} = req.query;

	async function getAdminInfo(userIdStr) {
		// 获取群组管理员id和头像
		return await dbQuery(`select id as user_id, avator as user_avator from user_table where id in (${userIdStr})`);
	}

	dbQuery(`
		select 
			g.group_id,g.group_name,g.group_avator,g.group_intro,g.admin,g.create_time,g.creator_id,g.group_public,count(m.user_id) as people_num
		from 
			group_table g,
			member_table m
		where
			g.group_id='${group_id}' and m.group_id='${group_id}'
	`)
	.then(async groupData => {
		if (groupData.length < 1) {
			res.send({code: 1, message: '群组不存在'})
			return;
		}
		const adminInfo = await getAdminInfo(groupData[0].admin);
		res.send({code: 1, message: '请求成功', data: { ...groupData[0], admin: adminInfo }});
	})
	.catch(err => {
		dbError(res, err);
	});
});
router.get('/member', function(req, res) {
	// 获取群组内的成员
	const {group_id} = req.query;

	const getMembers = () => {
		return new Promise((resolve, reject) => {
			dbQuery(`
				select u.id as user_id, u.name as user_name, u.avator as user_avator, g.join_time, '1' as member_type
				from user_table u, (select user_id, join_time from member_table where group_id='${group_id}') g 
				where g.user_id=u.id
			`)
			.then(data => resolve(data))
			.catch(err => reject(err));
		});
	}
	const getAdmins = () => {
		return new Promise((resolve, reject) => {
			dbQuery(`
				select admin,creator_id from group_table where group_id='${group_id}'
			`)
			.then(data => resolve(data))
			.catch(err => reject(err));
		});
	}

	Promise.all([getMembers(), getAdmins()])
	.then(data => {
		const memberInfo = data[0];
		const admins = data[1][0];
		if (memberInfo.length < 1 || admins.length < 1) {
			res.send({code: 0, message: '参数错误'});
			return;
		}
		admins.admin.split(',').forEach(val => {
			for (let i = 0; i < memberInfo.length; i++) {
				if (+val === +admins.creator_id && +val === +memberInfo[i].user_id) {
					memberInfo[i].member_type = '3';
					break;
				}
				if (+val === +memberInfo[i].user_id) {
					memberInfo[i].member_type = '2';
					break;
				}
			}
		});
		res.send({code: 1, message: '获取成功', data: memberInfo, adminsNum: admins.admin.split(',').length});
	})
	.catch(err => {
		dbError(res,err);
	});
});
router.get('/set', function(req, res) {
	const {group_id} = req.query;
	dbQuery(`select group_public from group_table where group_id='${group_id}'`)
	.then(data => {
		res.send({code: 1, message: '获取成功', data: data[0]});
	})
	.catch(err => {
		dbError(res, err);
	});
});
router.get('/quit', function(req, res) {
	const {group_id,user_id} = req.query;


	async function groupAdminChange() {
		// 如果此用户是退出群组的管理,删除管理中的对应用户id
		const data = await dbQuery(`select admin from group_table where group_id='${group_id}'`);
		const result = data[0].admin.split(',');
		if (result.includes(user_id)) {
			let userIndex = 0;
			for (let i = 0; i < result.length; i++) {
				if (result[i] === user_id) {
					userIndex = i;
					break;
				}
			}
			result.splice(userIndex, 1);
			return dbQuery(`update group_table set admin='${result.join(',')}' where group_id='${group_id}'`);
		} 
		return Promise.resolve(); 
	}
	function sendMessageToQuitUser() {
		// 发送一条系统消息退出群组。
		dbQuery(`
			insert into system_message_table 
			(user_id,message_content, message_type) 
			(
				select 
					'${user_id}' as user_id, 
					group_concat('你退出了群组',group_name) as message_content,
					'0' as message_content 
				from group_table 
				where group_id='${group_id}'
			)
		`)
		.then(data => {
			dbQuery(`
				select 
					user_id,message_id,message_content,message_type,send_time,checked 
				from system_message_table 
				where message_id='${data.insertId}'
			`)
			.then(selectData => {
				// 发送系统消息通知退出群组用户
				socketio().emit(`systemMessage${user_id}`, {...selectData[0],avator: "system_message",name: "系统消息"});
				// 发送退出事件,使groupList删除列表中的已退出群组
				socketio().toEmit(`groupQuit`, socketio().connectUser[user_id], {group_id});
			});
		});
	}
	function sendMessageToGroupAdmin() {
		// 向管理发送成员退出群组消息
		Promise.all([
			dbQuery(`select admin,group_name from group_table where group_id='${group_id}'`),
			dbQuery(`select name from user_table where id='${user_id}'`)
		])
		.then(data => {
			const resultAdmin = data[0][0].admin.split(',');
			// 向所有群组管理发送有用户退出群组消息
			resultAdmin.forEach(val => {
				dbQuery(`
					insert into system_message_table 
					(user_id,message_content,message_type) 
					values 
					('${val}','用户${data[1][0].name}退出群组${data[0][0].group_name}','0')
				`)
				.then(insertData => {
					socketio().emit(`systemMessage${val}`, {
						user_id:val,
						message_id: insertData.insertId,
						message_content: `用户${data[1][0].name}退出群组${data[0][0].group_name}`,
						message_type: '0',
						send_time: new Date(),
						checked: '0',
						avator: "system_message",
						name: "系统消息"
					})
				});
			});
		});
	}


	Promise.all([
		groupAdminChange(),
		dbQuery(`delete from member_table where group_id='${group_id}' and user_id='${user_id}'`)
	])
	.then(() => {
		sendMessageToQuitUser();
		sendMessageToGroupAdmin();
		

		res.send({code: 1, message: '退出成功'});
	})
	.catch(err => {
		dbError(res, err);
	});

});
router.get('/dismiss', async function(req, res) {
	const {group_id,user_id} = req.query;

	function sendNotifyToMember() {
		dbQuery(`select m.user_id, g.group_name from member_table m,group_table g where m.group_id='${group_id}' and g.group_id='${group_id}'`)
		.then(async data => {
			function sendSystemMessage() {
				// 为每个群成员发送系统消息,群组解散
				data.forEach(async val => {
					// socket所有成员发送groupQuit事件,因groupQuit无消息主体
					socketio().toEmit(`groupQuit`, socketio().connectUser[val.user_id], {group_id});
					// 数据库插入系统消息
					await dbQuery(`
						insert into system_message_table 
						(user_id,message_content,message_type) 
						values 
						('${val.user_id}','群主解散群组${val.group_name}','0')
					`)
					.then(insertData => {
						// 所有成员发送系统消息,群主解散了群组
						socketio().emit(`systemMessage${val.user_id}`, {
							user_id: val.user_id,
							message_id: insertData.insertId,
							message_content: `群主解散群组${val.group_name}`,
							message_type: '0',
							send_time: new Date(),
							checked: '0',
							avator: "system_message",
							name: "系统消息"
						});
					});
				});
			}
			function updateCancreateNum() {
				// 更新群主的可创建群组数
				dbQuery(`update user_table set can_create=can_create+1 where id='${user_id}'`)
				.catch(err => {
					console.log(err);
					console.log('移交群组后可创建群组数增加失败');
				});
			}

			await deleteGroupInfo()
			.then(() => {
				sendSystemMessage();
				updateCancreateNum();
				res.send({code: 1, message: '解散成功'});
			})
			.catch(err => {
				dbError(res, err);
			});
		})
		.catch(err => {
			dbError(res, err);
		});
	}
	function deleteGroupInfo() {
		const deleteMember = dbQuery(`delete from member_table where group_id='${group_id}'`);
		const deleteMessage = dbQuery(`delete from message_table where group_id='${group_id}'`);
		const deleteSystemMessage = dbQuery(`delete from system_message_table where about_group_id='${group_id}'`);
		const deleteGroup = dbQuery(`delete from group_table where group_id='${group_id}'`);

		return Promise.all([deleteMember, deleteMessage, deleteSystemMessage, deleteGroup])
	}


	client.get(user_id, (err, value) => {
		if (req.session.id === value) {
			// 是群主请求才进行删除
			// 先进行数据获取
			sendNotifyToMember();
		} else {
			res.send({code: 0, message: '需要群主进行操作'});
		}
	});
});

module.exports = router;

